package bank.db;

import bank.com.Bank;
import bank.dao.Account;
import java.sql.*;

public class DBoper {

	private Connection conn=null;
	private Statement st=null;
	private PreparedStatement pstmt=null;
	private ResultSet rs=null;
	
	  //*********************************连接数据库【解决乱码】*****************************
	public Connection getConnection(){
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/wangdb?useUnicode=true&characterEncoding=utf8", "root", "123456");
		   }catch(Exception e){
			   System.out.println("数据库连接失败");
			   
		   }
		return conn;
	}
	/*************prepareStatement插入数据*****************/
	public boolean  pstmtInsert(Account user){
		conn=getConnection();
		try{
			pstmt=conn.prepareStatement("insert into account(name,sex,password,id,card_type,balance,overdraw) values(?,?,?,?,?,?,?)");
		    pstmt.setString(1,user.getName() );
		    pstmt.setString(2,user.getSex() );
		    pstmt.setString(3,user.getPassword());
		    pstmt.setString(4,user.getId() );
		    pstmt.setInt(5,user.getCard_id() );
		    pstmt.setDouble(6,0);
		    pstmt.setDouble(7,user.getOverdraw());
		    pstmt.executeUpdate();
		    conn.close();
		    return true;
		}catch(SQLException e){
			System.out.println("用户添加失败");
			return false;
		}
	}
	//查询数据（卡号和密码）
	public boolean pstmtQuery(Account user){
		conn=getConnection();
		try{
			pstmt=conn.prepareStatement("select * from account where card_id=? and password=?");
			pstmt.setInt(1, user.getCard_id());
			pstmt.setString(2, user.getPassword());
			rs=pstmt.executeQuery();
			while(rs.next())
			{   int   id=rs.getInt("card_id");
				String username=rs.getString("name");
				String userpwd=rs.getString("password");
				String usersex=rs.getString("sex");
				double balance=rs.getDouble("balance");
				//System.out.println("用户id："+id+"   用户名："+username+"  密码是："+userpwd+"   性别是："+usersex+"   余额为："+balance);
				return true;
			}
			conn.close();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}
		
	}
//查询（卡号）
	public boolean pstmtQuery_cardid(Account user){
		conn=getConnection();
		try{
			pstmt=conn.prepareStatement("select * from account where card_id=? ");
			pstmt.setInt(1, user.getCard_id());
			rs=pstmt.executeQuery();
			while(rs.next())
			{   int   id=rs.getInt("card_id");
				String username=rs.getString("name");
				String userpwd=rs.getString("password");
				String usersex=rs.getString("sex");
				double balance=rs.getDouble("balance");
				//System.out.println("用户id："+id+"   用户名："+username+"  密码是："+userpwd+"   性别是："+usersex+"   余额为："+balance);
				return true;
			}
			conn.close();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}
		
	}
	//查询余额(卡号)
	public double pstmtQuery_balance(Account user){		
		conn=getConnection();
		try{
			pstmt=conn.prepareStatement("select balance from account where card_id=?");
			pstmt.setInt(1, user.getCard_id());
			rs=pstmt.executeQuery();
			while(rs.next()){
				double balance=rs.getDouble("balance");
				return balance;
			}
			
			conn.close();
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}
  	   return -1;
	}
	//存钱
	public boolean pstmtUpdate_save(Account user,double money){
		conn=getConnection();
		try{
			pstmt=conn.prepareStatement("select card_type,balance,overdraw from account where card_id=?");
			pstmt.setInt(1,user.getCard_id());
			rs=pstmt.executeQuery();
			while(rs.next()){
				int card_type=rs.getInt("card_type");
				double balance=rs.getDouble("balance");
				double overdraw=rs.getDouble("overdraw");
				if(card_type==0){
					pstmtUpdate_savebalance(user,money);
					return true;
				}
				if(card_type==1){
					if(balance>0)
						pstmtUpdate_savebalance(user,money);
					else if(overdraw+money<=1000)
							pstmtUpdate_saveoverdraw(user,money);
					else {
						pstmtUpdate_saveoverdraw(user,1000-overdraw);
						pstmtUpdate_savebalance(user,money+overdraw-1000);
					}
					return true;
				}
			}
			
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}

		return false;
		
	}
	//取钱
	public boolean pstmtUpdate_draw(Account user,double money){
		conn=getConnection();
		try{
				pstmt=conn.prepareStatement("select card_type,balance,overdraw from account where card_id=?");
				pstmt.setInt(1,user.getCard_id());
				rs=pstmt.executeQuery();
				while(rs.next()){
					int card_type=rs.getInt("card_type");
					double balance=rs.getDouble("balance");
					double overdraw=rs.getDouble("overdraw");
					if(card_type==0){
						if(money>balance)
							return false;
						else
							pstmtUpdate_drawbalance(user,money);
					}
					if(card_type==1){
						if(money>balance+overdraw)
							return false;
						else if(money<=balance)
							pstmtUpdate_drawbalance(user,money);
						else
						{
							pstmtUpdate_drawbalance(user,balance);
							pstmtUpdate_drawoverdraw(user,money-balance);
						}
					}
					return true;
				}
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}
		return false;
	}
	//转账
	public boolean pstmtUpdate_transfer(Account user,Account user1,double money){
		
	if(!pstmtUpdate_draw(user,money))
		return false;
	else{
			pstmtUpdate_save(user1,money);
		}
	return true;		
		
	}
	//取款余额(卡号)
	public boolean pstmtUpdate_drawbalance(Account user,double money){
		conn=getConnection();
		try{
			pstmt=conn.prepareStatement("update account set balance=balance-? where card_id=? ");
			pstmt.setDouble(1, money);
			pstmt.setInt(2, user.getCard_id());
			pstmt.executeUpdate();
			conn.close();
			return true;
			
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}
	}
	//取款透支(卡号)
	public boolean pstmtUpdate_drawoverdraw(Account user,double money){
		conn=getConnection();
		try{
			pstmt=conn.prepareStatement("update account set overdraw=overdraw-? where card_id=? ");
			pstmt.setDouble(1, money);
			pstmt.setInt(2, user.getCard_id());
			pstmt.executeUpdate();
			conn.close();
			return true;
			
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}
	}
	
	//存款余额(卡号)
	public boolean pstmtUpdate_savebalance(Account user,double money){
		conn=getConnection();
		try{
			pstmt=conn.prepareStatement("update account set balance=balance+? where card_id=? ");
			pstmt.setDouble(1, money);
			pstmt.setInt(2, user.getCard_id());
			pstmt.executeUpdate();
			conn.close();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
			
		}
		
	}

	//存款透支(卡号)
	public boolean pstmtUpdate_saveoverdraw(Account user,double money){
		conn=getConnection();
		try{
			pstmt=conn.prepareStatement("update account set overdraw=overdraw+? where card_id=? ");
			pstmt.setDouble(1, money);
			pstmt.setInt(2, user.getCard_id());
			pstmt.executeUpdate();
			conn.close();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
			
		}
		
	}

	//删除记录
	public boolean pstmtDelete(Account user){
		conn=getConnection();
		try{
			pstmt=conn.prepareStatement("delete from account where card_id=?");
			pstmt.setInt(1, user.getCard_id());
			pstmt.executeUpdate();
			conn.close();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}				
	}	


//改密码
public boolean pstmtUpdate_gaimi(Account user){
	conn=getConnection();
	try{
		pstmt=conn.prepareStatement("update account set password=? where card_id=? ");
		pstmt.setString(1, user.getPassword());
		pstmt.setInt(2, user.getCard_id());
		pstmt.executeUpdate();
		conn.close();
		return true;
	}catch(SQLException e){
		e.printStackTrace();
		return false;		
	}
	
}
}
